package dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import db.MySql;
import vo.boardVO;
import vo.companyFormVO;

public class companyFormDTO {

	private Connection conn = null;
	
	private companyFormVO vo = null;
	
	
	//페이지에 관련된 변수
	int listSize = 10;  //페이지당 표시되는 행수
	int blockSize = 5; //한블럭당 표시되는 페이지 수
	int totSize = 0;   //전체 게시물의 갯수
	int totPage = 0;   //전체 페이지 수
	int totBlock = 0;  //전체 블럭 수
	int nowPage = 0;   //현재 페이지
	int nowBlock = 0;  //현재 블럭
	int startNo = 0;   //리스트 목록의 시작 위치
	int endNo = 0;     //리스트 목록의 끝 위치
	int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
	int endPage = 0;   //현재 블럭에서 표시되는 끝 블럭 번호
	
	public companyFormDTO() {
		conn = new MySql().getConn();
		// TODO Auto-generated constructor stub
	}

	public companyFormDTO(companyFormVO v) {
		conn = new MySql().getConn();
		this.vo = v;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
	}
	
	public void voSet(companyFormVO v){
		this.vo = v;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
	}
	
	public companyFormVO find_number(companyFormVO cvo){
		companyFormVO v = cvo;
		String sql = "SELECT *  "
				+ " FROM semyadmin.messenger m JOIN semyadmin.departments d  "
				+ " JOIN semyadmin.employees e "
				+ " ON m.e_number = e.e_number AND e.d_code = d.d_code "
				+ " WHERE m.m_id = ? ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, v.getM_id());
			
			ResultSet rs = pst.executeQuery();
			
			while (rs.next()) {
				v.setE_number(rs.getLong("e_number"));	// 사원 번호
				v.setE_name(rs.getString("e_name"));		// 이름
				v.setE_position(rs.getString("e_position"));	// 직위
				v.setD_tname(rs.getString("d_tname"));		// 팀 이름
				System.out.println("this..." + rs.getString("d_tname"));
			}
				
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.getStackTrace();
		}
		return v;
	}
	
	public String user_name(Long e_number){
		conn = new MySql().getConn();
		String user_name = "";
		
		String sql = " SELECT * "
				+ " FROM semyadmin.employees "
				+ " WHERE e_number =  ? ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setLong(1, e_number);
			
			ResultSet rs = pst.executeQuery();
			
			while (rs.next()) {
				user_name += rs.getString("e_name");
				user_name += " [" + rs.getString("e_position") + "]";
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.getStackTrace();
		}
		
		return user_name;
	}
	
	public ArrayList<Long> member_list(String m_id){
		System.out.println("dto 내부 들어옴1");
		conn = new MySql().getConn();
		System.out.println("dto 내부 들어옴2");
		ArrayList<Long> al = new ArrayList<Long>();
		System.out.println("dto 내부 들어옴3");
		System.out.println("mid 값 " + m_id);
		String sql = "  SELECT * "
				+ " FROM semyadmin.messenger m JOIN semyadmin.employees e "
				+ " ON m.e_number = e.e_number "
				+ " WHERE m.m_id != ?  AND m.m_id != 'admin' "
				+ " AND e.e_number != ? "
				+ " AND e.e_number != ? "
				+ " AND e.e_number != ? "
				+ " AND e.e_number != ? "
				+ " AND e.e_number != ? ";
		
		try {
			System.out.println("pst전");
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, m_id);
			pst.setLong(2, vo.getC_draft_approbator_1());
			pst.setLong(3, vo.getC_draft_approbator_2());
			pst.setLong(4, vo.getC_draft_approbator_3());
			pst.setLong(5, vo.getC_draft_approbator_4());
			pst.setLong(6, vo.getC_draft_approbator_5());
			
			System.out.println("rs전");
			ResultSet rs = pst.executeQuery();
			
			System.out.println("while전");
			while (rs.next()) {
				System.out.println("while 내부");
				System.out.println(rs.getLong("e_number"));
				al.add(rs.getLong("e_number"));
			}
			System.out.println("while 종료");
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.getStackTrace();
		}
		System.out.println("결과 반환 전");
		return al;
	}
	
	public void auto_setting(){
		int count = 0;
		if (vo.getC_draft_approbator_1() != 0) {
			count++;
		}
		if (vo.getC_draft_approbator_2() != 0) {
			count++;
		}
		if (vo.getC_draft_approbator_3() != 0) {
			count++;
		}
		if (vo.getC_draft_approbator_4() != 0) {
			count++;
		}
		if (vo.getC_draft_approbator_5() != 0) {
			count++;
		}
		vo.setC_draft_approbator_count(count);
		System.out.println(count);
		System.out.println(vo.getC_draft_approbator_count());

	}
	
	public boolean save_form(){
		auto_setting();
		conn = new MySql().getConn();
		boolean value = false;
		
		String sql = "INSERT INTO  semyadmin.company_form ( "
				+ " c_form_number , c_draft_date , c_draft_start_date , "
				+ " c_draft_end_date , c_form_type , e_number , "
				+ " c_draft_approbator_1 , c_draft_approbator_2 , c_draft_approbator_3 , "
				+ " c_draft_approbator_4 , c_draft_approbator_5 , c_draft_title , "
				+ " c_draft_context , c_draft_back_count , c_draft_done , "
				+ " c_draft_now_state , c_draft_approbator_count )"
				+ " VALUE ( ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?)";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getC_form_number());
			pst.setString(2, vo.getC_draft_date());
			pst.setString(3, vo.getC_draft_start_date());
			pst.setString(4, vo.getC_draft_end_date());
			pst.setInt(5, vo.getC_form_type());
			
			pst.setLong(6, vo.getE_number());
			
			pst.setLong(7, vo.getC_draft_approbator_1());
			pst.setLong(8, vo.getC_draft_approbator_2());
			pst.setLong(9, vo.getC_draft_approbator_3());
			pst.setLong(10, vo.getC_draft_approbator_4());
			pst.setLong(11, vo.getC_draft_approbator_5());
			
			pst.setString(12, vo.getC_draft_title());
			pst.setString(13, vo.getC_draft_context());
			
			pst.setInt(14, vo.getC_draft_back_count());
			pst.setInt(15, vo.getC_draft_done());
			
			pst.setInt(16, (vo.getC_draft_now_state()+1));
			pst.setInt(17, vo.getC_draft_approbator_count());
			
			int result = pst.executeUpdate();
			
			if(result > 0){
				value = true;
			}
			
			pst.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return value;
	}

	public boolean submit_up() {
		conn = new MySql().getConn();
		boolean value = false;
		
		String sql = "";
		PreparedStatement pst = null;
		
		try {
			System.out.println("(vo.getC_draft_now_state()+1) : " + (vo.getC_draft_now_state()+1));
			System.out.println("vo.getC_draft_approbator_count() : " + vo.getC_draft_approbator_count());
			if((vo.getC_draft_now_state()+1) > vo.getC_draft_approbator_count()){
				sql = "UPDATE  semyadmin.company_form SET  "
						+ " c_draft_done =  1, c_draft_now_state =  ? "
						+ " WHERE form_number =? ";
				
				pst = conn.prepareStatement(sql);
				pst.setInt(1, (vo.getC_draft_now_state()+1));
				pst.setInt(2, vo.getForm_number());
			}
			else{
				sql = "UPDATE  semyadmin.company_form SET  "
						+ " c_draft_now_state =  ? "
						+ "  WHERE  form_number = ? ";
			
				pst = conn.prepareStatement(sql);
				pst.setInt(1, (vo.getC_draft_now_state()+1));
				pst.setInt(2, vo.getForm_number());
			}
			
			int result = pst.executeUpdate();

			if (result > 0) {
				System.out.println("submit_up 실행 됨!!");
				value = true;
			}

			pst.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return value;
	}
	
	public boolean submit_down() {
		conn = new MySql().getConn();
		boolean value = false;
		System.out.println("submit_down 들어옴");
		String sql = "UPDATE  semyadmin.company_form SET  "
				+ " c_draft_now_state =  0 , c_draft_back_count = ?"
				+ "  WHERE  form_number = ? ";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			int back_count = vo.getC_draft_back_count()+1;
			System.out.println("back_count" + back_count);
			System.out.println("vo.getC_draft_back_count()+1" + vo.getC_draft_back_count()+1);
			System.out.println("vo.getForm_number()" + vo.getForm_number());
			pst.setInt(1, back_count);
			pst.setInt(2, vo.getForm_number());
			int result = pst.executeUpdate();
			System.out.println("결과 반환 전");
			if (result > 0) {
				System.out.println("submit_down 실행 됨!!");
				value = true;
			}
			System.out.println("결과 반환 후 : " + value);
			pst.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return value;
	}
	
	public boolean modify_form(){
		conn = new MySql().getConn();
		boolean value = false;
		
		String sql = "UPDATE  semyadmin.company_form SET  "
				+ " c_form_number = ?, c_draft_date = ? , c_draft_start_date = ? , "
				+ " c_draft_end_date = ? , c_form_type = ? , e_number = ? , "
				+ " c_draft_approbator_1 = ? , c_draft_approbator_2 = ? , c_draft_approbator_3 = ? , "
				+ " c_draft_approbator_4 = ? , c_draft_approbator_5 = ? , c_draft_title = ? , "
				+ " c_draft_context = ? , c_draft_back_count = ? , c_draft_done = ? , "
				+ " c_draft_now_state = ? , c_draft_approbator_count = ? "
				+ "  WHERE  form_number = ? ";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getC_form_number());
			pst.setString(2, vo.getC_draft_date());
			pst.setString(3, vo.getC_draft_start_date());
			pst.setString(4, vo.getC_draft_end_date());
			pst.setInt(5, vo.getC_form_type());
			
			pst.setLong(6, vo.getE_number());
			
			pst.setLong(7, vo.getC_draft_approbator_1());
			pst.setLong(8, vo.getC_draft_approbator_2());
			pst.setLong(9, vo.getC_draft_approbator_3());
			pst.setLong(10, vo.getC_draft_approbator_4());
			pst.setLong(11, vo.getC_draft_approbator_5());
			
			pst.setString(12, vo.getC_draft_title());
			pst.setString(13, vo.getC_draft_context());
			
			pst.setInt(14, vo.getC_draft_back_count());
			pst.setInt(15, vo.getC_draft_done());
			
			pst.setInt(16, (vo.getC_draft_now_state()+1));
			pst.setInt(17, vo.getC_draft_approbator_count());
			
			pst.setInt(18, vo.getForm_number());
			int result = pst.executeUpdate();

			if (result > 0) {
				System.out.println("modify_form 실행 됨!!");
				value = true;
			}

			pst.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return value;
	}
	
	public void page() {
		String sql = "select count(*) cnt from semyadmin.company_form where e_number = ?"
				+ " or c_draft_approbator_1 = ? "
				+ " or c_draft_approbator_2 = ? "
				+ " or c_draft_approbator_3 = ? "
				+ " or c_draft_approbator_4 = ? "
				+ " or c_draft_approbator_5 = ? ";
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setLong(1, vo.getE_number());
			pst.setLong(2, vo.getE_number());
			pst.setLong(3, vo.getE_number());
			pst.setLong(4, vo.getE_number());
			pst.setLong(5, vo.getE_number());
			pst.setLong(6, vo.getE_number());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				totSize = rs.getInt("cnt");//6

				totPage = (int) Math.ceil(totSize * 1.0 / listSize);//3
				totBlock = (int) Math.ceil(totPage * 1.0 / blockSize);//1
				System.out.println("nowPage" + nowPage);
				nowBlock = (int) Math.ceil(nowPage * 1.0 / blockSize);//1
				
				endPage = nowBlock * blockSize;
				startPage = endPage - blockSize + 1;

				endNo = nowPage * listSize;
				startNo = endNo - listSize + 1;

				if (endPage > totPage)
					endPage = totPage;
				if (endNo > totSize)
					totSize = endNo;
			}
			
			System.out.println("totSize=" + totSize);
			
			System.out.println("totPage=" + totPage);
			System.out.println("totBlock=" + totBlock);
			System.out.println("nowBlock=" + nowBlock);
			
			System.out.println("totBlock=" + totBlock);
			System.out.println("nowBlock=" + nowBlock);
			
			System.out.println("endPage=" + endPage);
			System.out.println("startPage=" + startPage);
			
			System.out.println("endNo=" + endNo);
			System.out.println("startNo=" + startNo);

			rs.close();
			pst.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<companyFormVO> list() { // list 처리 + 검색
		conn = new MySql().getConn();
		ArrayList<companyFormVO> al = new ArrayList<companyFormVO>();
		
		page();
		
		String sql = " SELECT * "
				+ " FROM( "
				+ " SELECT c.*, @RNUM := @RNUM +1 AS RNUM , "
				+ "  e.e_name, e.e_position "
				+ " FROM (SELECT @RNUM := 0) tmp INNER JOIN "
				+ " semyadmin.company_form c JOIN "
				+ " semyadmin.employees e "
				+ " ON c.e_number = e.e_number "
				+ " WHERE c.e_number = ? OR "
				+ " c.c_draft_approbator_1 = ? OR "
				+ " c.c_draft_approbator_2 = ? OR "
				+ " c.c_draft_approbator_3 = ? OR "
				+ " c.c_draft_approbator_4 = ? OR "
				+ " c.c_draft_approbator_5 = ? "
				+ " )A "
				+ " WHERE A.RNUM BETWEEN ? AND ? "
				+ " ORDER BY A.c_draft_date DESC  ";
		
		System.out.println("startNo : "  + startNo);
		System.out.println("endNo : "  + endNo);
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setLong(1, vo.getE_number());
			pst.setLong(2, vo.getE_number());
			pst.setLong(3, vo.getE_number());
			pst.setLong(4, vo.getE_number());
			pst.setLong(5, vo.getE_number());
			pst.setLong(6, vo.getE_number());
			pst.setInt(7, startNo);
			pst.setInt(8, endNo);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				companyFormVO v = new companyFormVO();
				
				v.setE_number(rs.getLong("e_number"));
				v.setE_position(rs.getString("e_position"));
				v.setE_name(rs.getString("e_name"));
				
				v.setC_draft_back_count(rs.getInt("c_draft_back_count"));
				v.setC_draft_context(rs.getString("c_draft_context"));
				v.setC_draft_date(rs.getString("c_draft_date"));
				v.setC_draft_done(rs.getInt("c_draft_done"));
				
				v.setC_draft_end_date(rs.getString("c_draft_end_date"));
				v.setC_draft_now_state(rs.getInt("c_draft_now_state"));
				v.setC_draft_start_date(rs.getString("c_draft_start_date"));
				v.setC_draft_title(rs.getString("c_draft_title"));
				v.setC_form_number(rs.getString("c_form_number"));
				v.setC_form_type(rs.getInt("c_form_type"));
				v.setForm_number(rs.getInt("form_number"));
				
			
				
				v.setC_draft_approbator_count(rs.getInt("c_draft_approbator_count"));
				v.setC_draft_approbator_1(rs.getLong("c_draft_approbator_1"));
				v.setC_draft_approbator_2(rs.getLong("c_draft_approbator_2"));
				v.setC_draft_approbator_3(rs.getLong("c_draft_approbator_3"));
				v.setC_draft_approbator_4(rs.getLong("c_draft_approbator_4"));
				v.setC_draft_approbator_5(rs.getLong("c_draft_approbator_5"));
				al.add(v);
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return al;
	}
	

	public companyFormVO form_view_info(int form_number){
		companyFormVO v = new companyFormVO();
		
		String sql = " SELECT c.* ,  e.e_name, e.e_position , d.d_tname"
				+ " FROM semyadmin.company_form c JOIN "
				+ " semyadmin.employees e JOIN "
				+ " semyadmin.departments d"
				+ " ON c.form_number = ? "
				+ " WHERE c.e_number = e.e_number AND e.d_code = d.d_code";
		
				try {
					PreparedStatement pst = conn.prepareStatement(sql);
					pst.setLong(1, form_number);
					
					ResultSet rs = pst.executeQuery();
					while (rs.next()) {
						
						v.setE_number(rs.getLong("e_number"));
						v.setE_position(rs.getString("e_position"));
						v.setE_name(rs.getString("e_name"));
						v.setD_tname(rs.getString("d_tname"));
						
						v.setC_draft_back_count(rs.getInt("c_draft_back_count"));
						v.setC_draft_context(rs.getString("c_draft_context"));
						v.setC_draft_date(rs.getString("c_draft_date"));
						v.setC_draft_done(rs.getInt("c_draft_done"));
						
						v.setC_draft_end_date(rs.getString("c_draft_end_date"));
						v.setC_draft_now_state(rs.getInt("c_draft_now_state"));
						v.setC_draft_start_date(rs.getString("c_draft_start_date"));
						v.setC_draft_title(rs.getString("c_draft_title"));
						v.setC_form_number(rs.getString("c_form_number"));
						v.setC_form_type(rs.getInt("c_form_type"));
						v.setForm_number(rs.getInt("form_number"));
						
					
						
						v.setC_draft_approbator_count(rs.getInt("c_draft_approbator_count"));
						v.setC_draft_approbator_1(rs.getLong("c_draft_approbator_1"));
						v.setC_draft_approbator_2(rs.getLong("c_draft_approbator_2"));
						v.setC_draft_approbator_3(rs.getLong("c_draft_approbator_3"));
						v.setC_draft_approbator_4(rs.getLong("c_draft_approbator_4"));
						v.setC_draft_approbator_5(rs.getLong("c_draft_approbator_5"));
					}
					
					rs.close();
					pst.close();
					conn.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
				
		return v;
	}
	
	public long find_my_number(String m_id){
		conn = new MySql().getConn();
		long my_e_number = 0;
		String sql = "SELECT e_number  "
				+ " FROM semyadmin.messenger "
				+ " WHERE m_id = ? ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, m_id);
			
			ResultSet rs = pst.executeQuery();
			
			while (rs.next()) {
				my_e_number = rs.getLong("e_number");
			}
				
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.getStackTrace();
		}
		return my_e_number;
	}
	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
}
